package cn.imvc.app.repositories;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cn.imvc.app.entities.Orders;

public interface OrdersRepository extends JpaRepository<Orders, Integer> {

	public Orders findByCode(String code);
	//下面2个sql，state为0的时候all为true，查找全部状态订单
	//state为10的时候查询待付款状态，实际是创建订单（10）和支付中（20）都可以被查询到所以使用(:state=10 and g.state=20)
	//state为21的时候查询待发货状态，实际是付款成功状态（21）
	// 2016年11月10日13:57:04 state为30的时候查询待发货状态，实际是待发货状态（30）
	//state为31的时候查询确认收货状态，实际是待收货状态（31）
	//state为40的时候查询待评价状态，实际是确认收货（40）、系统确认收货（41）、订单完成（60）和已完成（70）都可以去评价所以使用(:state=40 and (g.state=41 or g.state=60 or g.state=70))
	//待评价还有一个要求是不能评价过，所以使用(:state=40 and g.commentState is null or :state!=40)
	@Query("select distinct o from Orders o,GoodsOrder g where o.id=g.order.id "
			+ "and (g.state=:state or :all=true or (:state=40 and (g.state=41 or g.state=60 or g.state=70)) or (:state=10 and g.state=20)) "
			+ "and (:state=40 and g.commentState is null or :state!=40) "
			+ "and o.member.id=:memberId")
	public Page<Orders> findByState(@Param("state") Byte state, @Param("all") boolean all,
			@Param("memberId") Integer memberId, Pageable pageable);

	@Query("select distinct o from Orders o,GoodsOrder g,Member m "
			+ "where o.id=g.order.id and (g.state=:state or :all=true or (:state=40 and (g.state=41 or g.state=60 or g.state=70)) or (:state=10 and g.state=20))"
			+ "and (:state=40 and g.commentState is null or :state!=40) "
			+ "and o.member.id=m.id "
			+ "and m.parent.id = :memberId "
			)
	public Page<Orders> findByStateOneLevel(@Param("state") Byte state, @Param("all") boolean all,
			@Param("memberId")Integer memberId,Pageable pageable);
	
	@Query("select distinct o from Orders o,GoodsOrder g "
			+ "where o.id=g.order.id and (g.state=:state or :all=true or (:state=40 and (g.state=41 or g.state=60 or g.state=70)) or (:state=10 and g.state=20))"
			+ "and (:state=40 and g.commentState is null or :state!=40) "		
			+ "and o.member.id in (:memberId) ")
	public Page<Orders> findByStateSomeLevel(@Param("state") Byte state, @Param("all") boolean all,
			@Param("memberId")List<Integer> memberId,Pageable pageable);
	
	
	@Query("select distinct o from Orders o,GoodsOrder g,Member m "
			+ "where o.id=g.order.id and (g.state=:state or :all=true or (:state=40 and (g.state=41 or g.state=60 or g.state=70)) or (:state=10 and g.state=20))"
			+ "and (:state=40 and g.commentState is null or :state!=40) "
			+ "and o.member.id=m.id "
			+ "and m.guid like concat(:guid,',%') ")
	public Page<Orders> findByStateAllLevel(@Param("state") Byte state, @Param("all") boolean all,
			@Param("guid")String guid,Pageable pageable);
}
